import pymysql

from sqlalchemy import create_engine



class MysqlUtils:
    DB_USER = 'user_rpa'
    DB_PASSWORD = '123456'
    DB_HOST = '192.168.8.10'
    DB_PORT = '3306'
    
    DB_CHARSET = 'utf8mb4'

    def __init__(self, DB_NAME="rpa"):
        self.connection = pymysql.connect(
            host=MysqlUtils.DB_HOST,
            user=MysqlUtils.DB_USER,
            password=MysqlUtils.DB_PASSWORD,
            database=DB_NAME,
            port=int(MysqlUtils.DB_PORT),
            charset=MysqlUtils.DB_CHARSET
        )
        self.engine = create_engine(f'mysql+pymysql://user1:123456@192.168.8.10:3306/{DB_NAME}?charset=utf8mb4')

    def __del__(self):
        if self.connection:
            self.connection.close()

    @staticmethod
    def mysql_connect():
        try:
            engine = create_engine(
                f'mysql+pymysql://{MysqlUtils.DB_USER}:{MysqlUtils.DB_PASSWORD}@{MysqlUtils.DB_HOST}:{MysqlUtils.DB_PORT}/{MysqlUtils.DB_NAME}?charset={MysqlUtils.DB_CHARSET}',
                pool_size=300, max_overflow=100, connect_args={'connect_timeout': 60}
            )
            return engine
        except Exception as e:
            print(e)

    def get_merchant_id(self, browserOauth):
        target_cursor = self.connection.cursor()
        sql = """SELECT merchantID 
                            FROM amz_store 
                            WHERE browserOauth = %s"""
        target_cursor.execute(sql, (browserOauth,))
        self.connection.close
        ret = target_cursor.fetchone()
        if ret:
            return ret[0]
        return None, None

    def get_marketplace_id(self, browserOauth):
        target_cursor = self.connection.cursor()
        sql = """SELECT marketplace_id 
                            FROM amz_store 
                            WHERE browserOauth = %s"""
        target_cursor.execute(sql, (browserOauth,))
        self.connection.close
        ret = target_cursor.fetchone()
        if ret:
            return ret[0]
        return None, None

    def get_all_store_list(self):
        target_cursor = self.connection.cursor()
        sql = """select browserOauth from amz_store"""
        target_cursor.execute(sql)
        ret = target_cursor.fetchall()
        store_id_list = []
        if not ret:
            return store_id_list
        store_id_list = [browserOauth_obj[0] for browserOauth_obj in ret]
        return store_id_list


if __name__ == '__main__':
    m = MysqlUtils()
    target_cursor = m.connection.cursor()
    sql = "select principal_info from asin where date=(select max(date) from asin) and sid='{}'".format(11010)
    target_cursor.execute(sql)
    ret = target_cursor.fetchall()
    print(ret)
